<?php

/**
 * This is the model class for table "resource_timing".
 *
 * The followings are the available columns in table 'resource_timing':
 * @property integer $id
 * @property integer $resource
 * @property string $eventtype
 * @property integer $eventid
 * @property integer $allDay
 * @property string $eventOn
 * @property string $starttime
 * @property string $endtime
 * @property integer $duration
 * @property string $note
 *
 * The followings are the available model relations:
 * @property Users $resource0
 */
class ResourceTiming extends CActiveRecord {

    const TYPE_SERVICE = 'Service';
    const TYPE_HOLIDAY = 'Holiday';

    /**
     * @return string the associated database table name
     */
    public function tableName() {
        return 'resource_timing';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules() {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            /* array('resource, duration', 'numerical', 'integerOnly'=>true), */
            array('eventtype', 'length', 'max' => 50),
            array('eventOn, starttime, endtime', 'safe'),
            array('id, resource, eventtype, allDay, eventOn, starttime, endtime, duration', 'safe', 'on' => 'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations() {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'resources' => array(self::BELONGS_TO, 'User', 'resource'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'id' => 'ID',
            'resource' => 'Resource',
            'eventtype' => 'Eventtype',
            'allDay' => 'All Day',
            'eventOn' => 'Event On',
            'starttime' => 'Starttime',
            'endtime' => 'Endtime',
            'duration' => 'Duration',
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     *
     * Typical usecase:
     * - Initialize the model fields with values from filter form.
     * - Execute this method to get CActiveDataProvider instance which will filter
     * models according to data in model fields.
     * - Pass data provider to CGridView, CListView or any similar widget.
     *
     * @return CActiveDataProvider the data provider that can return the models
     * based on the search/filter conditions.
     */
    public function search() {
        // @todo Please modify the following code to remove attributes that should not be searched.

        $criteria = new CDbCriteria;

        $criteria->compare('id', $this->id);
        $criteria->compare('resource', $this->resource);
        $criteria->compare('eventtype', $this->eventtype, true);
        $criteria->compare('allDay', $this->allDay);
        $criteria->compare('eventOn', $this->eventOn, true);
        $criteria->compare('starttime', $this->starttime, true);
        $criteria->compare('endtime', $this->endtime, true);
        $criteria->compare('duration', $this->duration);

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
        ));
    }

    public static function getAllEvents($date, $resource) {
        $sql = 'SELECT * FROM resource_timing WHERE eventOn =:date and resource = :resource_id or resource IS NULL';
        return Yii::app()->db->createCommand($sql)->queryAll(true, array(':resource_id' => $resource, 'date' => $date));
    }

    public static function getAllHolidays($date) {
        $sql = 'SELECT * FROM resource_timing WHERE eventOn =:date and eventtype = :eventtype';
        return Yii::app()->db->createCommand($sql)->queryAll(true, array(':eventtype' => self::TYPE_HOLIDAY, 'date' => $date));
    }

    /**
     * 
     * @param string $start - must be DateTime String of format 2014-02-03 10:30:00
     * @param string $end  must be DateTime String of format 2014-02-03 10:30:00
     * @param string $resourceId - user id of employee
     * @return boolean is available or not TRUE/FALSE
     */
    public static function checkAvailability($date,$start, $end, $resourceId) {
        
        $q="select * from resource_timing where (resource = :resource_id or resource is null) and eventOn='".$date."' and allday=true";
         $results = Yii::app()->db->createCommand($q)->queryAll(true, array(':resource_id' => $resourceId));
         if(isset($results)&&  sizeof($results)>0){
             return FALSE;
         }
        $q1 = "select eventOn, count('eventOn') from resource_timing where (resource = :resource_id or resource IS NULL) and (starttime between  '" . $start . "' and  '" . $end . "') GROUP BY eventOn";
        $results1 = Yii::app()->db->createCommand($q1)->queryAll(true, array(':resource_id' => $resourceId));
         if(isset($results1)&&  sizeof($results1)>0){
             return FALSE;
         }
        $q2 = "select eventOn, count('eventOn') from resource_timing where (resource = :resource_id or resource IS NULL) and (endtime between  '" . $start . "' and  '" . $end . "') GROUP BY eventOn";
        $results2 = Yii::app()->db->createCommand($q2)->queryAll(true, array(':resource_id' => $resourceId));
         if(isset($results2)&&  sizeof($results2)>0){
             return FALSE;
         }
        $q3 = "select * from resource_timing where  (resource = :resource_id or resource IS NULL) and (starttime<= '".$start."' and endtime>= '".$start."')";
        $results3 = Yii::app()->db->createCommand($q3)->queryAll(true, array(':resource_id' => $resourceId));
         if(isset($results3)&&  sizeof($results3)>0){
             return FALSE;
         }
        $q4 = "select * from resource_timing where  (resource = :resource_id or resource IS NULL) and (starttime<= '".$end."' and endtime>= '".$end."')";
        $results4 = Yii::app()->db->createCommand($q4)->queryAll(true, array(':resource_id' => $resourceId));
         if(isset($results4)&&  sizeof($results4)>0){
             return FALSE;
         }
         return true;
    }

    /**
     * Returns the static model of the specified AR class.
     * Please note that you should have this exact method in all your CActiveRecord descendants!
     * @param string $className active record class name.
     * @return ResourceTiming the static model class
     */
    public static function model($className = __CLASS__) {
        return parent::model($className);
    }

}
